package controllers;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lib.UtilsInput;
import models.Reporte4;

import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import play.db.DB;
import play.mvc.Controller;

public class Reporte_4 extends Controller {
	
	public static void Reporte_4() {
		if(Security.isAuthenticated())
			render();
		else
			Application.login();
    }
	
	public static void getReporte4(String from,String to,String selec){
		String query = "select PRO_SERVICIO,CLI_NOMBRES,PRO_MONTO,PRO_AVANCE from SIGPC_PROYECTO sigpro join SIGPC_CLIENTE sigcli on sigpro.CLI_ID = sigcli.CLI_ID order by PRO_MONTO desc ";
		String str = null;
		Gson gson = new Gson();
    	Connection conn = DB.getConnection();
    	
    	//Lista de objetos 
    	List<Reporte4> listReporte4 = new ArrayList<Reporte4>();
    	System.out.println(from);
    	
    	try {
    		if (from != null &&  to != null && selec != null)
    		{
    			if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase("") && selec.equalsIgnoreCase("0"))
        			query = "select PRO_SERVICIO,CLI_NOMBRES,PRO_MONTO,PRO_AVANCE from SIGPC_PROYECTO sigpro join SIGPC_CLIENTE sigcli on sigpro.CLI_ID = sigcli.CLI_ID WHERE PRO_FECHA_INI between '" + from + "' AND '" + to + "' order by PRO_MONTO desc ";
    			
    			if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase("") && !selec.equalsIgnoreCase("0"))
    			query = "select PRO_SERVICIO,CLI_NOMBRES,PRO_MONTO,PRO_AVANCE from SIGPC_PROYECTO sigpro join SIGPC_CLIENTE sigcli on sigpro.CLI_ID = sigcli.CLI_ID WHERE PRO_FECHA_INI between '" + from + "' AND '" + to + "' AND rownum <= "+ selec +"order by PRO_MONTO desc ";
    		}	
    			
    		
        	ResultSet rs = conn.createStatement().executeQuery(query);
        	
        	 while (rs.next()) {
        		 Reporte4 reporte4 = new Reporte4(rs.getString("CLI_NOMBRES"),rs.getString("PRO_SERVICIO"),rs.getFloat("PRO_MONTO"),rs.getString("PRO_AVANCE"));
        		 listReporte4.add(reporte4);  	
        	 }
    		
        	 str = "{\"success\":true ,\"records\":" + listReporte4.size()
     				+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte4)
     				+ "}";

     		renderJSON(str);
     		
        	        	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT
	public static void  download_report(String from, String to, String nproyectos, String formato) {
	    Map reportParams = new HashMap();
	    
	   if (formato != null)
	   {
	  reportParams.put("from", from);
	  reportParams.put("to",  to);
	  reportParams.put("rownum",  nproyectos);
	    
	  if (formato.equalsIgnoreCase("html"))
		renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_4", reportParams,formato)));
		
	  else
		  renderBinary(lib.BaseJasperReport.generateReport("reporte_4", reportParams,formato), "reporte_4."+ formato);
	   
	   }
	  }

}
